# !/bin/bash
# 获取处理数据日期值，如果传递直接赋值，否则获取前一天日期
if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

DIM_MD_AREAS_SQL =
"drop function if exists con_geohash;

create function con_geohash as 'net.bwie.udf.convert_geohash'
using jar '/warehouse/zg6_yuekao-1.0-SNAPSHOT.jar';

insert into jtp_app_dim.dim_md_areas partition (dt='${data_date}')
WITH tmp_area as (
    select ID, AREANAME, PARENTID, BD09_LNG, BD09_LAT
    from jtp_app_ods.ods_md_areas),
     tmp1 as
         (select ID,
                 AREANAME as province
          from tmp_area
          where PARENTID = 0),
     tmp2 as (
         select t1.province,
                ta.AREANAME as city,
                ta.ID
         from tmp_area ta
                  join tmp1 t1 on t1.ID = ta.PARENTID),
     tmp3 as (
         select ta.ID,
                t2.province,
                t2.city,
                ta.AREANAME as district
         from tmp_area ta
                  join tmp2 t2 on ta.PARENTID = t2.id)
-- insert overwrite table jtp_app_dim.dim_md_areas partition (dt='${data_date}')
select ta.id,
       province,
       city,
       district,
       ta.AREANAME                        as street,
       ta.BD09_LAT,
       ta.BD09_LNG,
       con_geohash(BD09_LNG, BD09_LAT, 6) as geohash
from tmp_area ta
         join tmp3 t3 on ta.PARENTID = t3.ID
where BD09_LAT is not null
  and BD09_LNG is not null;


"
# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_MD_AREAS_SQL}"